package com.cyc.basic.controller;

import cn.hutool.core.date.LocalDateTimeUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.cyc.basic.pojo.dto.NoSatisfyDto;
import com.cyc.basic.pojo.dto.ZjUserDto;
import com.cyc.basic.pojo.poi.UserWorkRecord;
import com.cyc.basic.pojo.poi.UserWorkCommonPlan;
import com.cyc.basic.pojo.poi.enums.EnumNoSatisfyReason;
import com.cyc.basic.service.poi.IPoiService;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigInteger;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import static org.apache.commons.lang3.Conversion.intToByteArray;

/**
 * @description:
 * @author chenyc
 * @date 2023/4/18 14:15
 */
@RestController
@RequestMapping("/poi")
public class PoiController {

    @Resource
    private IPoiService poiService;

    private static List<String> FILE_NAME = Arrays.asList("userName", "type", "dept", "number", "jobTitle", "userId");
    private static List<String> FILE_TITLE = Arrays.asList("姓名", "考勤组", "部门", "工号", "职位", "UserId");


    @RequestMapping("/import")
    public List<ZjUserDto> importExcel(MultipartFile workFile, MultipartFile workPlanFile) throws Exception {
        List<UserWorkRecord> userWorkRecordList = EasyExcel.read(workFile.getInputStream()).head(UserWorkRecord.class).doReadAllSync();
        List<UserWorkCommonPlan> userWorkCommonPlanList = EasyExcel.read(workPlanFile.getInputStream()).head(UserWorkCommonPlan.class).doReadAllSync();
        List<ZjUserDto> zjUserDtoList = poiService.analyseData(userWorkRecordList, userWorkCommonPlanList);

//        // 写入新的excel中
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();
        SXSSFSheet sxssfSheet = sxssfWorkbook.createSheet("考勤数据");
        Class<UserWorkRecord> clazz = UserWorkRecord.class;
        // 获取表头
        List<String> titleList = getTitleList(clazz);
        // 创建表头
        createTitle(sxssfSheet, titleList);
        // 塞入数据到表格中
        createData(sxssfWorkbook, sxssfSheet, userWorkRecordList, zjUserDtoList);
        //获取配置文件中保存对应excel文件的路径，本地也可以直接写成 D:\testDir 路径
        String folderPath = "D:\\testDir";
        //创建上传文件目录
        File folder = new File(folderPath);
        //如果文件夹不存在创建对应的文件夹
        if (!folder.exists()) {
            folder.mkdirs();
        }
        //设置文件名
        String fileName = LocalDateTimeUtil.format(LocalDateTime.now(), "HHmmss") + "测试报表导出" + ".xlsx";
        String savePath = folderPath + File.separator + fileName;
        OutputStream fileOut = new FileOutputStream(savePath);
        sxssfWorkbook.write(fileOut);
        fileOut.close();
        return zjUserDtoList;
    }

    /**
     * 使用原表格解析
     * @param workFile
     * @param workPlanFile
     * @return
     * @throws Exception
     */
    @RequestMapping("/importV2")
    public List<ZjUserDto> importV2(MultipartFile workFile, MultipartFile workPlanFile) throws Exception {
        List<UserWorkRecord> userWorkRecordList = EasyExcel.read(workFile.getInputStream()).head(UserWorkRecord.class).doReadAllSync();
        List<UserWorkCommonPlan> userWorkCommonPlanList = EasyExcel.read(workPlanFile.getInputStream()).head(UserWorkCommonPlan.class).doReadAllSync();
        List<ZjUserDto> zjUserDtoList = poiService.analyseData(userWorkRecordList, userWorkCommonPlanList);
        Map<String, ZjUserDto> userNameAndUserDtoMap = zjUserDtoList.stream().collect(Collectors.toMap(ZjUserDto::getUserName, zjUserDto -> zjUserDto, (k1, k2) -> k1));
        InputStream inputStream = workFile.getInputStream();
        Workbook wb = new XSSFWorkbook(inputStream);
        Sheet sheetAt = wb.getSheetAt(0);
        int lastRowNum = sheetAt.getLastRowNum();
        for (int y = 1; y < lastRowNum; y++) {
            Row row = sheetAt.getRow(y);
            // 第一列是姓名
            List<String> errDateList = new ArrayList<>();
            Map<String, EnumNoSatisfyReason> dayAndReasonTypeMap = new HashMap<>();
            if (row == null) {
                continue;
            }
            Cell nameCell = row.getCell(0);
            String userName = nameCell.getStringCellValue();
            // 获取当前行对应人的考勤异常情况
            ZjUserDto zjUserDto = userNameAndUserDtoMap.get(userName);
            if (zjUserDto != null) {
                List<NoSatisfyDto> noSatisfyDtoList = zjUserDto.getNoSatisfyDtoList();
                errDateList = noSatisfyDtoList.stream().map(NoSatisfyDto::getDay).collect(Collectors.toList());
                dayAndReasonTypeMap = noSatisfyDtoList.stream().collect(Collectors.toMap(NoSatisfyDto::getDay, NoSatisfyDto::getNoSatisfyReason));
            }
            //获取每一列值
            for (int j = 6; j < row.getLastCellNum(); j++) {
                Cell cell = row.getCell(j);
                int columnIndex = cell.getColumnIndex();
                String day = String.valueOf(columnIndex - 5);
                if (errDateList.contains(day)) {
                    CellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    cell.setCellStyle(cellStyle);
                    short colorIndex = IndexedColors.YELLOW.getIndex();
                    EnumNoSatisfyReason enumNoSatisfyReason = dayAndReasonTypeMap.get(day);
                    switch (enumNoSatisfyReason) {
                        case NO_WORK_RECORD:
                            colorIndex = IndexedColors.YELLOW.getIndex();
                            break;
                        case NO_BEGIN_RECORD:
                            colorIndex = IndexedColors.GREEN.getIndex();
                            break;
                        case NO_END_RECORD:
                            colorIndex = IndexedColors.GREEN.getIndex();
                            break;
                        case BEGIN_BE_LATE:
                            colorIndex = IndexedColors.RED.getIndex();
                            break;
                        case END_LEAVE_EARLY:
                            colorIndex = IndexedColors.RED1.getIndex();
                            break;
                        default: NO_WORK_RECORD:
                            colorIndex = IndexedColors.YELLOW.getIndex();
                            break;
                    }
                    cellStyle.setFillForegroundColor(colorIndex);
                }
            }
        }
        //获取配置文件中保存对应excel文件的路径，本地也可以直接写成 D:\testDir 路径
        String folderPath = "D:\\testDir";
        //创建上传文件目录
        File folder = new File(folderPath);
        //如果文件夹不存在创建对应的文件夹
        if (!folder.exists()) {
            folder.mkdirs();
        }
        //设置文件名
        String fileName = LocalDateTimeUtil.format(LocalDateTime.now(), "HHmmss") + "测试报表导出" + ".xlsx";
        String savePath = folderPath + File.separator + fileName;
        OutputStream fileOut = new FileOutputStream(savePath);
        wb.write(fileOut);
        fileOut.close();
        return zjUserDtoList;
    }

    private static List<String> getDayList(Row titleRow) {
        List<String> dayTitleList = new ArrayList<>();
        for (Cell cell : titleRow) {
            if (cell == null) {
                continue;
            }
            CellType cellType = cell.getCellType();
            if (CellType.NUMERIC.equals(cellType)) {
                double numericCellValue = cell.getNumericCellValue();
                dayTitleList.add(String.valueOf(numericCellValue));
            }
        }
        return dayTitleList;
    }

    private void createData(SXSSFWorkbook sxssfWorkbook, SXSSFSheet sxssfSheet, List<UserWorkRecord> userWorkRecordList, List<ZjUserDto> zjUserDtoList) throws IllegalAccessException {
        if (CollectionUtils.isEmpty(userWorkRecordList)) {
            return;
        }
        Map<String, ZjUserDto> userNameAndUserDtoMap = zjUserDtoList.stream().collect(Collectors.toMap(ZjUserDto::getUserName, zjUserDto -> zjUserDto, (k1, k2) -> k1));
        for (int j = 1; j <= userWorkRecordList.size(); j++) {
            SXSSFRow row = sxssfSheet.createRow(j);
            // 反射获取属性
            UserWorkRecord userWorkRecord = userWorkRecordList.get(j - 1);
            ZjUserDto zjUserDto = userNameAndUserDtoMap.get(userWorkRecord.getUserName());
            Field[] declaredFields = userWorkRecord.getClass().getDeclaredFields();
            for (int z = 0; z < declaredFields.length; z++) {
                // 获取列上的注解名
                Field declaredField = declaredFields[z];
                // 注解上的值
                String annotationValue = declaredField.getAnnotation(ExcelProperty.class).value()[0];
                List<String> errDateList = new ArrayList<>();
                if (zjUserDto != null) {
                    List<NoSatisfyDto> noSatisfyDtoList = zjUserDto.getNoSatisfyDtoList();
                    errDateList = noSatisfyDtoList.stream().map(NoSatisfyDto::getDay).collect(Collectors.toList());
                }
                SXSSFCell cell = row.createCell(z);
                // 向cell中插入值。
                setValue(cell, declaredField, userWorkRecord);
                // 设置单元格颜色
                if (errDateList.contains(annotationValue)) {
                    CellStyle cellStyle = sxssfWorkbook.createCellStyle();
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    cellStyle.setFillForegroundColor(IndexedColors.PINK1.getIndex());
                    cell.setCellStyle(cellStyle);
                }
            }
        }
    }


    /**
     * 向Excel中放入数据
     *
     * @param cell          列
     * @param declaredField 反射获取属性值
     * @param object        实体类的属性
     * @throws IllegalAccessException
     */
    private void setValue(SXSSFCell cell, Field declaredField, Object object) throws IllegalAccessException {
        String type = declaredField.getType().getName();
        declaredField.setAccessible(true);
        if (type.endsWith("String")) {
            cell.setCellValue((String) declaredField.get(object));
        } else if (type.endsWith("Boolean") || type.endsWith("boolean")) {
            cell.setCellValue(declaredField.get(object) == null ? false : (Boolean) declaredField.get(object));
        } else if (type.endsWith("LocalDate") || type.endsWith("Date")) {
            cell.setCellValue(declaredField.get(object) == null ? null : (Date) declaredField.get(object));
        } else if (type.endsWith("Long") || type.endsWith("long")) {
            cell.setCellValue(declaredField.get(object) == null ? 0L : (Long) declaredField.get(object));
        } else if (type.endsWith("Integer") || type.endsWith("int")) {
            cell.setCellValue(declaredField.get(object) == null ? 0 : (Integer) declaredField.get(object));
        } else if (type.endsWith("Float") || type.endsWith("float")) {
            cell.setCellValue(declaredField.get(object) == null ? 0F : (Float) declaredField.get(object));
        }
    }

    /**
     * 获取表头
     * @param clazz
     * @return
     */
    private List<String> getTitleList(Class clazz) {
        List<String> list = new ArrayList();
        Field[] declaredFields = clazz.getDeclaredFields();
        for (Field field : declaredFields) {
            ExcelProperty declaredAnnotation = field.getDeclaredAnnotation(ExcelProperty.class);
            list.add(declaredAnnotation.value()[0]);
        }
        return list;
    }

    private void createTitle(SXSSFSheet sxssfSheet, List<String> titleList) {
        SXSSFRow rowTitle = sxssfSheet.createRow(0);
        // 多少个表头数据创建多少行列表头
        for (int i = 0; i < titleList.size(); i++) {
            SXSSFCell cell = rowTitle.createCell(i);
            cell.setCellValue(titleList.get(i));
        }
    }

}
